package com.gsk.europepharma.salesReport.struts.actions;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.struts.action.Action;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;
import org.hibernate.Query;
import org.hibernate.Session;

import com.gsk.europepharma.salesReport.hibernate.dao.tbSpBoSRSectoDAO;

public class ExportGridDoctorsExcelUga extends Action {
	
	public ActionForward execute(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) throws Exception {
		
		// Parametres 
		String codeUga = request.getParameter("codeUga");
		
		String cs_division_string = request.getParameter("cs_division");
		Integer cs_division = Integer.parseInt(cs_division_string);
		String periodeActivite = request.getParameter("periodeActivite");
		String specialite = request.getParameter("specialite");
		String segments = request.getParameter("segments");
		// Fin des parametres 
		
		response.reset();
        response.setHeader("Content-Disposition", "attachment; filename=\"" + "docteurs.xls" + "\"");
        
		HSSFWorkbook wb = new HSSFWorkbook();
	    HSSFSheet sheet = wb.createSheet("Docteurs");

	    HSSFFont font = wb.createFont();
	    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
	    font.setColor(HSSFColor.WHITE.index);
	    
	    HSSFCellStyle cellStyle = wb.createCellStyle();
	    cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	    cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
	    cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
	    cellStyle.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index);
	    cellStyle.setFont(font);
	    
	    HSSFCellStyle cellStylePeer = wb.createCellStyle();
	    cellStylePeer.setFillPattern(CellStyle.SOLID_FOREGROUND);
	    cellStylePeer.setFillForegroundColor(HSSFColor.WHITE.index);
	    
	    HSSFCellStyle cellStyleOdd = wb.createCellStyle();
	    cellStyleOdd.setFillPattern(CellStyle.SOLID_FOREGROUND);
	    cellStyleOdd.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);

	    HSSFRow row = sheet.createRow(0);
	    
	    HSSFCell cell = row.createCell((short) 0);
	    cell.setCellValue("Nom");
	    cell.setCellStyle(cellStyle);
	    
	    cell = row.createCell((short) 1);
	    cell.setCellValue("Prénom");
	    cell.setCellStyle(cellStyle);
	    
	    cell = row.createCell((short) 2);
	    cell.setCellValue("Code postal");
	    cell.setCellStyle(cellStyle);
	    
	    cell = row.createCell((short) 3);
	    cell.setCellValue("Ville");
	    cell.setCellStyle(cellStyle);
	    
	    cell = row.createCell((short) 4);
	    cell.setCellValue("Spécialité");
	    cell.setCellStyle(cellStyle);
	    
	    // Requete SQL
	    tbSpBoSRSectoDAO salesDAO = new tbSpBoSRSectoDAO();
		Session session = salesDAO.getSession();
					
		Query req = null;
		
		String strReq = salesDAO.QUERY_QUERY_LISTE_CONTACT_UGA;
		
		req = session.getNamedQuery(strReq); 
		req.setString("uga", codeUga);
		ArrayList result = new ArrayList(req.list());
		Iterator iter = result.iterator();
		
		int i = 1;
		
		HSSFCellStyle cellStyleLine;
		int j = 0;
		
		while (iter.hasNext()) {
			
			Object[] val = (Object[]) iter.next();
			
			if ((j % 2) == 0) {
				cellStyleLine = cellStylePeer;
			} else {
				cellStyleLine = cellStyleOdd;
			}
			j++;
			
			row = sheet.createRow(i);
			
			cell = row.createCell((short) 0);
			cell.setCellValue(val[4].toString());
			cell.setCellStyle(cellStyleLine);
			
			cell = row.createCell((short) 1);
			cell.setCellValue(val[5].toString());
			cell.setCellStyle(cellStyleLine);
			
			cellStyleLine.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			
			cell = row.createCell((short) 2);
			cell.setCellValue(Double.parseDouble(val[7].toString()));
			cell.setCellStyle(cellStyleLine);
			
			cellStyleLine.setAlignment(HSSFCellStyle.ALIGN_LEFT);
			
			cell = row.createCell((short) 3);
			cell.setCellValue(val[8].toString());
			cell.setCellStyle(cellStyleLine);
			
			cellStyleLine.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			
			cell = row.createCell((short) 4);
			cell.setCellValue(val[9].toString());
			cell.setCellStyle(cellStyleLine);
			
			cellStyleLine.setAlignment(HSSFCellStyle.ALIGN_LEFT);
			
			i++;
		}
		
		sheet.autoSizeColumn(0);
		sheet.autoSizeColumn(1);
		sheet.autoSizeColumn(2);
		sheet.autoSizeColumn(3);
		sheet.autoSizeColumn(4);

	    try {
	      wb.write(response.getOutputStream());
	    } catch (FileNotFoundException e) {
	      e.printStackTrace();
	    } catch (IOException e) {
	      e.printStackTrace();
	    }
	    
        response.getOutputStream().flush();
	    
	    return null;
	}

}
